package club.samubird.simpledatahandlekit.db.core;

import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlProvider;
import org.springframework.jdbc.core.StatementCallback;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.sql.BatchUpdateException;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.StringJoiner;

/**
 * @author sam
 * @date 2019/11/5 10:59
 * @description 功能描述
 */
@Slf4j
@Service
public class JdbcExtensions {

	private static final String SAVE_OR_UPDATE_SQL_TEMPLATE = "INSERT INTO %s (%s) VALUES (%s) ON DUPLICATE KEY UPDATE %s ";

	@Autowired
	private JdbcTemplate jdbcTemplate;

	/**
	 * 批量 插入或更新数据 (存在则更新，否则则插入)
	 *
	 * @param updateDataList 需要更新的数据
	 * @param tableName      表名称
	 * @return 返回影响行数
	 */
	public int[] batchSaveOrUpdate(final List<Map> updateDataList, String tableName) {
		if (log.isDebugEnabled()) {
			log.debug("要插入或更新的数据是 ： {}", updateDataList);
		}

		class BatchSaveOrUpdateStatementCallback implements StatementCallback<int[]>, SqlProvider {

			@Nullable
			private String currSql;

			@Override
			public String getSql() {
				return this.currSql;
			}

			@Override
			public int[] doInStatement(Statement stmt) throws SQLException, DataAccessException {
				int[] rowsAffected = new int[updateDataList.size()];
				if (JdbcUtils.supportsBatchUpdates(stmt.getConnection())) {
					for (Map map : updateDataList) {
						String oneSql = getSaveOrUpdateSql(map, tableName);
						// 拼接sql
						this.currSql = appendSql(this.currSql, oneSql);
						stmt.addBatch(oneSql);
					}
					try {
						rowsAffected = stmt.executeBatch();
					} catch (BatchUpdateException ex) {
						String batchExceptionSql = null;
						for (int i = 0; i < ex.getUpdateCounts().length; i++) {
							if (ex.getUpdateCounts()[i] == Statement.EXECUTE_FAILED) {
								String exceptionSql = getSaveOrUpdateSql(updateDataList.get(i), tableName);
								batchExceptionSql = appendSql(batchExceptionSql, exceptionSql);
							}
						}
						if (StringUtils.hasLength(batchExceptionSql)) {
							this.currSql = batchExceptionSql;
						}
						throw ex;
					}
				} else {
					for (int i = 0, l = updateDataList.size(); i < l; i++) {
						String sql = getSaveOrUpdateSql(updateDataList.get(i), tableName);
						this.currSql = sql;
						if (!stmt.execute(sql)) {
							rowsAffected[i] = stmt.getUpdateCount();
						} else {
							throw new InvalidDataAccessApiUsageException("Invalid batch SQL statement: " + sql);
						}
					}
				}
				return rowsAffected;
			}

			private String appendSql(@Nullable String sql, String statement) {
				return (StringUtils.isEmpty(sql) ? statement : sql + "; " + statement);
			}


			public String getSaveOrUpdateSql(Map map, String tableName) {
				StringJoiner columns = new StringJoiner(",");
				StringJoiner insertPart = new StringJoiner(",");
				StringJoiner updatePart = new StringJoiner(",");
				map.forEach(
						(k, v) -> {
							columns.add(String.valueOf(k));
							insertPart.add("'" + v + "'");
							updatePart.add(" " + k + " = '" + v + "' ");
						}
				);
				return String.format(SAVE_OR_UPDATE_SQL_TEMPLATE, tableName, columns.toString(), insertPart.toString(), updatePart.toString());
			}
		}

		int[] result = jdbcTemplate.execute(new BatchSaveOrUpdateStatementCallback());

		if (log.isDebugEnabled()) {
			log.debug("数据 {} ：对表 {} 插入或更新的结果是 ： {}", updateDataList, tableName, result);
		}

		return result;
	}

}
